# -*- coding: utf-8 -*-
# @Time: 2024/12/4 4:16
# @Author: foxhuty
# @File: exam_room_2025.py

import pandas as pd

file_path = r'D:\data_test\成都十一中高2024级高一半期考试.xlsx'
# file_path = r'D:\data_test\高三半期考试成绩年级.xlsx'

df = pd.read_excel(file_path)


# print(df.head())
def exam_rooms_arrangement(data, num_in_room):
    """
    用于教室安排，生成考生所在考室号和座位号，同时生成考生座签
    :param num_in_room: 考室人数
    :param data: 用于生成考室安排的原始成绩表
    :return: 考生所在考室号和座位号df和考生座签df
    """
    # 按总分排序分配考室号
    data.sort_values(by='总分', ascending=False, inplace=True)
    # 生成考室号和座位号
    data['考室号'] = [f'第{i // num_in_room + 1}考室' for i in range(data.shape[0])]
    data['座位号'] = [f'{i % num_in_room + 1}' for i in range(data.shape[0])]
    # 获取考室df
    exam_rooms_df = data.loc[:, ['班级', '姓名', '考号', '考室号', '座位号']]
    exam_rooms_df.reset_index(drop=True, inplace=True)
    # 生成考生座签
    exam_seat = exam_rooms_df.copy()
    for i in range(exam_seat.shape[0]):
        exam_seat.loc[i + 0.5] = ['班级', '姓名', '考号', '考室号', '座位号']
    exam_seat.sort_index(inplace=True, ignore_index=True)
    with pd.ExcelWriter('D:\\成绩统计结果\\exam_info.xlsx') as writer:
        exam_seat.to_excel(writer, index=False, sheet_name='座签号')
        exam_rooms_df.to_excel(writer, index=False, sheet_name='考室安排表')
    return exam_rooms_df, exam_seat


if __name__ == '__main__':
    exam_rooms_arrangement(df, 35)
